import os
import chardet
import re
import pandas as pd
from sqlalchemy import create_engine
class rushData(object):
    # 检测文件编码
    def detect_encoding(self, file_path):
        with open(file_path, 'rb') as f:
            file_encoding = chardet.detect(f.read())
        return file_encoding['encoding']
    # 检测文件名称是否正确
    def check_file_name(self, file_path):
        pattern = r'^JD_\d+'
        re_result = re.findall(pattern, file_path)
        return re_result
    # 检测csv文件中缺少的配置字段,并将字段没有缺失的数据保存到数据库
    def get_file_colums(self, config_data, data, new_col, item,username,password,database):
        csv_columns = data.columns.tolist()
        # print(config_data.values())
        miss_columns = (set(config_data.values()) - set(csv_columns))
        if miss_columns:
            print(f"The following columns are missing from the {item} file:")
            print("="*50)
            for col in miss_columns:
                print(col)
        else:
            print(f"{item} all required columns are present in the CSV file.")
            self.insert_data(config_data, data, new_col, username,password,database)

    # 插入数据
    def insert_data(self, config_data, data, new_col, username,password,database):
        insert_data = data.loc[:, [i for i in config_data.values()]]
        insert_data["store_code"] = new_col
        try:
            # 创建MySQL引擎
            engine = create_engine(f'mysql+mysqlconnector://{username}:{password}@127.0.0.1:3306/{database}')
            # 数据插入
            insert_data.to_sql('items', engine, if_exists='replace', index=False)
        except Exception as e:
            print(e)



def main():
    # 初始化类
    a = rushData()
    # 文件路径
    root_path = (r"./item")
    # 配置文件关键词
    config_str = 'propties'
    # 列出文件路径下的所有子目录
    file_list = os.listdir(root_path)
    for item in file_list:
        if config_str in item:
            config_path = os.path.join(root_path, item)
            result_encoding = a.detect_encoding(config_path)
            # 获取内容配置字典
            with open(config_path, "r", encoding=result_encoding) as f:
                content = f.read()
                content_dict = {i.split("=")[-1]: i.split("=")[0] for i in content.split("\n")}
        result_re = a.check_file_name(item)
        csv_path = os.path.join(root_path, item)
        if bool(result_re):
            store_code = result_re[0]
            detected1_encoding = a.detect_encoding(csv_path)
            df = pd.read_csv(csv_path, encoding=detected1_encoding)
            a.get_file_colums(content_dict, df, store_code, item, username="数据库用户名", password="密码", database="数据库")
            # 修改文件名称
            new_item = item.split(".")[0] + ".COMPLETED"
            new_file_path = os.path.join(root_path, new_item)
            os.rename(csv_path,new_file_path)

        else:
            if"propties" in item:
                print(f"{item} is config file")
            else:
                new_item = item.split(".")[0] + ".FAILED"
                new_file_path = os.path.join(root_path, new_item)
                os.rename(csv_path, new_file_path)




if __name__ == '__main__':
    main()